import json
import pymysql

# 读取news数据，并写入数据库
# 导入数据库成功，总共4736897条记录

def reviewdata_insert(db):

    with open('D:/0822_news_lingyu_1600.json', encoding='utf-8') as f:
        i = 0
        while True:
            i += 1
            print(u'正在载入第%s行......' % i)
            lines = f.readline()  # 使用逐行读取的方法

            # print(result)

            try:
                review_text = json.loads(lines)  # 解析每一行数据
                author = review_text['queryKW'].split('AND')[0].replace('(("', '').replace('")', '').strip()
                result = []
                if "textSrc" in review_text:
                    result.append(
                        (author, review_text['titleSrc'], review_text['pubTime'], review_text['url'].strip(),
                         review_text['mediaNameSrc'].strip(), review_text['textSrc']))
                else:
                    result.append(
                        (author, review_text['titleSrc'], review_text['pubTime'], review_text['url'].strip(),
                         review_text['mediaNameSrc'].strip(), ""))



                inesrt_re = "insert into news_json(userData, titleSrc, pubTime, url, mediaNameSrc, textSrc) values (%s, %s, %s, %s,%s, %s)"
                cursor = db.cursor()
                cursor.executemany(inesrt_re, result)
                db.commit()
            except Exception as e:
                # db.rollback()
                # print(str(e))
                # filename = 'd:\\write_data.txt'
                # with open(filename, 'a') as ff:
                #     ff.write(lines)
                    # ff.write('\n')

                result2 = []
                result2.append((i))
                inesrt_re = "insert into news_0805_copy(sq) values (%s)"
                cursor = db.cursor()
                cursor.executemany(inesrt_re, result2)
                db.commit()
                # break\
                pass



if __name__ == "__main__":  # 起到一个初始化或者调用函数的作用
    # db = pymysql.connect("localhost:3398", "root", "123456", "dataService", charset='utf8')
    db = pymysql.Connect(
        host='127.0.0.1',
        port=3306,
        user='root',
        passwd='123456',
        db='scholar-preview',
        charset='utf8'
    )
    cursor = db.cursor()
    # prem(db)
    reviewdata_insert(db)
    cursor.close()